﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
using Microsoft.Win32;

namespace lp1415_5605
{
    /// <summary>
    /// Módulo referente à leitura do ficheiro Excel em formato .xls 
    /// e criação/escrita da base de dados sqlite3
    /// </summary>
    class DadosTabela
    {
        //Nome da Tabela da Base de Dados
        private String tableName = "Dados";

        //Colunas da Tabela
        private String Id = "Id";
        private String Ano = "Ano";
        private String IPC_Global = "IPC_Global";
        private String Variacao_Anual = "Variacao_Anual";
        private String Remuneracao_minima_mensal = "Remuneracao_minima_mensal";
        private String Remuneracao_maxima_mensal = "Remuneracao_maxima_mensal";
        private String PIB_per_capita_anual = "PIB_per_capita_anual";
        private String Rendimento_nacional_bruto_per_capita_anual = "Rendimento_nacional_bruto_per_capita_anual";
        private String Rendimento_disponivel_bruto_per_capita_anual = "Rendimento_disponivel_bruto_per_capita_anual";
        private String Remuneracoes_per_capita_anual = "Remuneracoes_per_capita_anual";

        /// <summary>
        /// Pede ao utilizador para seleccionar um Ficheiro de Excel e cria uma Base de Dados com os valores lidos
        /// </summary>
        /// <param name="databaseName">Nome da Base de Dados</param>
        public void createDb(String databaseName)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();

            //Filtro para apenas aparecerem na Dialog Ficheiros de Excel
            openFileDialog.Filter = "Livro do Excel 97-2003 (*.xls)|*.xls|Livro do Excel (*.xlsx)|*.xlsx|Todos os Ficheiros (*.*)|*.*";
            
            //Titulo que surje no topo da Dialog
            openFileDialog.Title = "Seleccione o Ficheiro de Excel";

            if (openFileDialog.ShowDialog() == true)
            {
                //Abre a conexão à bd. Se a bd não existir cria uma nova
                SQLiteConnection conn = new SQLiteConnection();
                conn.ConnectionString = "Data Source=" + databaseName + ";New=True;Compress=True;Version=3;Synchronous=Off";
                conn.Open();

                //Cria um objecto do tipo SQLiteCommand, que permite exectar comandos SQL na BD criada
                SQLiteCommand cmd = new SQLiteCommand();
                cmd = conn.CreateCommand();

                //Apagamos a tabela da BD caso já exista
                cmd.CommandText = String.Format("DROP TABLE IF EXISTS " + tableName);
                //Executa a Query SQL previamente atribuida ao comando
                cmd.ExecuteNonQuery();

                //Criamos uma nova tabela com uma chave primaria do tipo INTEGER AUTOINCREMENT e todos os outros campos são do tipo Text por omissão
                String commandSql = "CREATE TABLE " + tableName + "(" + Id + " INTEGER PRIMARY KEY AUTOINCREMENT" + "," + Ano + "," + IPC_Global + "," + Variacao_Anual + "," + Remuneracao_minima_mensal + "," + Remuneracao_maxima_mensal + "," + PIB_per_capita_anual + "," + Rendimento_nacional_bruto_per_capita_anual + "," + Rendimento_disponivel_bruto_per_capita_anual + "," + Remuneracoes_per_capita_anual + ")";
                cmd.CommandText = commandSql;
                cmd.ExecuteNonQuery();

                //Caminho do ficheiro Excel a ler
                String path = openFileDialog.FileName;
                
                //Lê ficheiro de Excel selecionado, apartir da Linha nº 2, entre as Colunas 2 e 10 (Inclusivé)
                String[][] excelContent = this.readExcelFile(path, 2, 2, 10);

                //Percorre todas as linhas lidas e adiciona na tabela
                for(int i = 0; i < excelContent.Length; i++)
                {
                    String[] row = excelContent[i];

                    cmd.CommandText = "INSERT INTO " + tableName + "(" + Ano + "," + IPC_Global + "," + Variacao_Anual + "," + Remuneracao_minima_mensal + "," + Remuneracao_maxima_mensal + "," + PIB_per_capita_anual + "," + Rendimento_nacional_bruto_per_capita_anual + "," + Rendimento_disponivel_bruto_per_capita_anual + "," + Remuneracoes_per_capita_anual + ") VALUES ( " + String.Format("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]) + " )";
                    cmd.ExecuteNonQuery();
                }

                //Fecha a ligação à base de dados. É neste momento que é feito commit às alterações na BD.
                conn.Close();
            }
        }

        /// <summary>
        /// Lê ficheiro de Excel
        /// Provided by: http://csharp.net-informations.com/excel/csharp-read-excel.htm
        /// </summary>
        /// <param name="filePath">Caminho do Ficheiro</param>
        /// <param name="initRow">Numero da linha onde vai começar a ler. (Útil para excluir o Cabeçalho)</param>
        /// <param name="minColumnIndex">Coluna de onde vai começar a ler</param>
        /// <param name="maxColumnIndex">Coluna onde vai terminar de ler</param>
        /// <returns>Todo o conteúdo do ficheiro Excel, de acordo com os parâmetros de entrada </returns>
        public String[][] readExcelFile(String filePath, int initRow, int minColumnIndex, int maxColumnIndex)
        {
            ExcelApplication xlApp = new ExcelApplication();
            Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Range range = xlWorkSheet.UsedRange;

            //Numero de linhas = NumertoTotal_Linhas - Numero_Linha_Inicial + 1. (O +1 é para ler desde a initRow até ao Numero total de linhas inclusivé)
            int linesCount = range.Rows.Count - initRow + 1;
            String[][] excelContent = new String[linesCount][];

            //Numero de Colunas = Diferença entre indice das Colunas. (O +1 serve para ler entre as colunas inclusivé)
            int columnsCount = maxColumnIndex - minColumnIndex + 1;

            for (int rCnt = initRow; rCnt <= range.Rows.Count; rCnt++)
            {
                excelContent[rCnt - initRow] = new String[columnsCount];

                for (int cCnt = minColumnIndex; cCnt <= maxColumnIndex; cCnt++)
                {
                    excelContent[rCnt - initRow][cCnt - minColumnIndex] = range.Cells[rCnt, cCnt].Value + "";
                }
            }

            xlWorkBook.Close(false, null, null);
            xlApp.Quit();

            //Liberta os objectos Excel da memória 
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            return excelContent;
        }

        /// <summary>
        /// Liberta os objectos da memória
        /// Provided by: http://csharp.net-informations.com/excel/csharp-read-excel.htm
        /// </summary>
        /// <param name="obj">Objecto a ser libertado</param>
        private void releaseObject(object obj)
        {
            try
            {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}